import pymysql
from pymysql.err import InterfaceError, OperationalError, ProgrammingError
import time


class mysql():
    def __init__(self, host, user, password, database, port, max_retries=3, retry_delay=2):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.max_retries = max_retries  # 最大重试次数
        self.retry_delay = retry_delay  # 重试间隔(秒)
        self.con = None
        self.cur = None
        self._connect()  # 初始化连接

    def _connect(self):
        """建立新连接，带重试机制"""
        retries = 0
        while retries < self.max_retries:
            try:
                if self.con:
                    try:
                        self.con.close()
                    except:
                        pass
                self.con = pymysql.connect(
                    host=self.host,
                    user=self.user,
                    password=self.password,
                    database=self.database,
                    port=self.port
                )
                self.cur = self.con.cursor()
                return  # 连接成功则退出重试循环
            except (InterfaceError, OperationalError) as e:
                retries += 1
                if retries >= self.max_retries:
                    raise ConnectionError(f"超过最大重试次数({self.max_retries})，无法连接数据库: {str(e)}")
                time.sleep(self.retry_delay)  # 重试前等待
        raise ConnectionError("未知错误，无法建立数据库连接")

    def _check_conn(self):
        """检查连接是否有效，无效则重连"""
        try:
            self.con.ping(reconnect=False)  # 测试连接，不自动重连
        except (InterfaceError, OperationalError):
            self._connect()  # 重连

    def _execute_with_retry(self, func, sql, *args):
        """带重试机制的执行方法"""
        retries = 0
        while retries < self.max_retries:
            try:
                self._check_conn()  # 执行前检查连接
                return func(sql, *args)
            except (InterfaceError, OperationalError, ProgrammingError) as e:
                retries += 1
                if retries >= self.max_retries:
                    raise e
                time.sleep(self.retry_delay)
                self._connect()  # 尝试重连

    def select(self, sql):
        """查询操作，带重试"""
        def _query(sql):
            self.cur.execute(sql)
            return self.cur.fetchall()
        return self._execute_with_retry(_query, sql)

    def delete(self, sql):
        """删除操作，带重试"""
        def _delete(sql):
            self.cur.execute(sql)
            self.con.commit()
            return self.cur.rowcount
        try:
            return self._execute_with_retry(_delete, sql)
        except Exception as e:
            self.con.rollback()
            raise e

    # 可以类似地为其他操作(如insert、update)添加带重试的方法

    def close(self):
        """手动关闭连接"""
        if self.cur:
            self.cur.close()
        if self.con:
            self.con.close()